﻿using BlackList.Utils;
using MySql.Data.MySqlClient;
using QWManage.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;

namespace QWManage.MySqlSync
{
    internal class MySqlDAL
    {
        public static decimal ReadDecimal(MySqlDataReader reader, string name)
        {
            return (decimal)reader[name];
        }

        public static decimal? ReadDecimalNull(MySqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            return (decimal)reader[name];
        }

        public static int ReadInt(MySqlDataReader reader, string name)
        {
            return (int)reader[name];
        }

        public static int? ReadIntNull(MySqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            return (int)reader[name];
        }
        public static long ReadLong(MySqlDataReader reader, string name)
        {
            return (long)reader[name];
        }
        public static long? ReadLongNull(MySqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            return (long)reader[name];
        }

        public static bool ReadBool(MySqlDataReader reader, string name)
        {
            return (bool)reader[name];
        }

        public static string ReadString(MySqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            else return ((string)reader[name]).Trim();
        }

        public static DateTime? ReadDateTimeNull(MySqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            else return (DateTime)reader[name];
        }

        public static int GetRecordsTotal(string conn_str)
        {
            using (MySqlConnection conn = new MySqlConnection(conn_str))
            {
                conn.Open();
                string sql = "select count(id) from member";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                object obj = cmd.ExecuteScalar();
                return (int)(long)obj;
            }
        }

        public static void Synchoronous(string owner, string mysql_conn_str, string mssql_conn_str, ProgressBar progressBar)
        {
          
             List<MemberInfo> memberInfos = new List<MemberInfo>();
             //1、先从Mysql数据库获取数据
             using (MySqlConnection mysql_conn = new MySqlConnection(mysql_conn_str))
             {
                 mysql_conn.Open();
                 string sql = "select * from member  where father_id like '%@%'";
                 MySqlCommand cmd = new MySqlCommand(sql, mysql_conn);
                 using (MySqlDataReader reader = cmd.ExecuteReader())
                 {
                     while (reader.Read())
                     {
                         if (progressBar.Value != progressBar.Maximum) progressBar.Value += 1;
                         MemberInfo memberInfo = GetMemberInfoFromMySql(reader);
                         memberInfo.Owner = owner;
                         if(memberInfo.father_id!=null&&memberInfo.father_id.ToString().Contains("@"))    //针对只是有邀请人，并且是企微的进行存
                         memberInfos.Add(memberInfo);
                     }
                 }
             }


             //新增1  如果数据不存在就入库
             progressBar.Value = 0;
             progressBar.Minimum = 0;
             progressBar.Maximum = memberInfos.Count;
             foreach (MemberInfo memberInfo in memberInfos)
             {
                 progressBar.Value += 1;

                 int? obj = MsSqlDAL.GetECOMemberID(mssql_conn_str, memberInfo.wx_id, owner);
                 if (obj != null)
                 {
                     //后面需要对存在的在进行更新
                  //  user.ID = obj.ID;
                    // Update(user);
                 }
                 else {
                     using (SqlConnection mssql_conn = new SqlConnection(mssql_conn_str))
                     {
                         mssql_conn.Open();
                         string insert_sql = MsSqlDAL.GetInsertSql();
                         SqlCommand mssql_cmd = new SqlCommand(insert_sql, mssql_conn);
                         MsSqlDAL.SetParameters(memberInfo, mssql_cmd);
                         mssql_cmd.ExecuteNonQuery();
                     }

                 }

             }
            

            //新增2  如果数据不存在就入库
            //原理。根据  ECO_Member  中的  father_id   匹配   [QW_Customers]表中  [F_UserID] 对应的的威信id的用户的  名字一样的

            
            using (SqlConnection conn = new SqlConnection(mssql_conn_str))
            {
                conn.Open();
                //string sql = $"select wx_id,wx_name,wx_img,father_id  from ECO_Member where Exuserid is null";
                //SqlCommand cmd = new SqlCommand(sql, conn);
                //cmd.Parameters.AddWithValue("@wxid", wxid);
                //cmd.Parameters.AddWithValue("@owner", owner);
                //object obj = cmd.ExecuteReader.ExecuteScalar();
                //if (obj != null && obj != DBNull.Value)
                //{
                //    return Convert.ToInt32(obj);
                //}
                string filePath = Path.Combine(Application.StartupPath, $"files/download");
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select wx_id,wx_name,wx_img,father_id  from ECO_Member where Exuserid is null";
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string wx_id = dt.Rows[i]["wx_id"].ToString();
                        string wx_img = dt.Rows[i]["wx_img"].ToString();
                        string wx_name = dt.Rows[i]["wx_name"].ToString();
                        string father_id = dt.Rows[i]["father_id"].ToString();
                        cmd.Parameters.Clear();
                        cmd.CommandText = "select * from View_QW_Users_Customers2 where qwid=@qwid and Name=@name";
                        cmd.Parameters.Add(new SqlParameter("@qwid", father_id));
                        cmd.Parameters.Add(new SqlParameter("@name", wx_name));
                        sda = new SqlDataAdapter(cmd);
                        DataSet ds2 = new DataSet();
                        sda.Fill(ds2);
                        DataTable dt2 = ds2.Tables[0];
                        if (dt2.Rows.Count == 1)
                        {
                            string exuserid = dt2.Rows[0]["Exuserid"].ToString();
                            cmd.CommandText = "update QW_Customers set W_IsInECO=1 where Exuserid=@exuserid";
                            cmd.Parameters.AddWithValue("@exuserid", exuserid);
                            cmd.ExecuteNonQuery();


                            cmd.CommandText = "update ECO_Member set Exuserid=@exuserid where wx_id=@wx_id";
                            cmd.Parameters.Add(new SqlParameter("@wx_id", wx_id));
                            cmd.ExecuteNonQuery();
                        }
                        else if (dt2.Rows.Count > 1)
                        {
                            if (!wx_img.EndsWith("/0"))
                            {
                                wx_img = $"{wx_img.Substring(0, wx_img.LastIndexOf("/"))}/0";                                
                            }
                            string wx_img_file = ImageUtils.DownloadFile(wx_img, filePath);
                            foreach (DataRow dr in dt2.Rows)
                            {
                                string exuserid = dr["Exuserid"].ToString();
                                string headImg = dr["Avatar"].ToString();
                                if(string.IsNullOrEmpty(headImg)) break;
                                string headImg_file = ImageUtils.DownloadFile(headImg, filePath);
                                var sim = ImageUtils.Compare_Hist(wx_img_file, headImg_file);
                                if (sim > 0.9)
                                {
                                    cmd.Parameters.Clear();
                                    cmd.CommandText = "update QW_Customers set W_IsInECO=1 where Exuserid=@exuserid";
                                    cmd.Parameters.AddWithValue("@exuserid", exuserid);
                                    cmd.ExecuteNonQuery();

                                    cmd.CommandText = "update ECO_Member set Exuserid=@exuserid where wx_id=@wx_id";
                                    cmd.Parameters.Add(new SqlParameter("@wx_id", wx_id));
                                    cmd.ExecuteNonQuery();
                                    break;
                                }
                            }
                        }

                    }

                }


            }




            /*
            //2、清空Sql Server数据库中属于 Owner 和 memberInfo.wx_img 的数据
            //省去更新操作，因为判断是否在CEO，主要依靠 wx_img 字段
            foreach (MemberInfo memberInfo in memberInfos)
            {
                if (string.IsNullOrEmpty(memberInfo.wx_img)) continue;
                using (SqlConnection mssql_conn = new SqlConnection(mssql_conn_str))
                {
                    mssql_conn.Open();
                    string delete_sql = "delete from [ECO_Member] where Owner=@Owner and wx_img=@wx_img";
                    SqlCommand mssql_cmd = new SqlCommand(delete_sql, mssql_conn);
                    mssql_cmd.Parameters.AddWithValue("@Owner", owner);
                    mssql_cmd.Parameters.AddWithValue("@wx_img", memberInfo.wx_img);
                    mssql_cmd.ExecuteNonQuery();
                }
            }
            //3、数据同步到Sql Server数据库中
            progressBar.Value = 0;
            progressBar.Minimum = 0;
            progressBar.Maximum = memberInfos.Count;
            foreach (MemberInfo memberInfo in memberInfos)
            {
                progressBar.Value += 1;
                using (SqlConnection mssql_conn = new SqlConnection(mssql_conn_str))
                {
                    mssql_conn.Open();
                    string insert_sql = MsSqlDAL.GetInsertSql();
                    SqlCommand mssql_cmd = new SqlCommand(insert_sql, mssql_conn);
                    MsSqlDAL.SetParameters(memberInfo, mssql_cmd);
                    mssql_cmd.ExecuteNonQuery();
                }
            }
            //3、在表中先获取 Avatar ，头像数据
            Dictionary<string, List<string>> dic = MsSqlDAL.GetOwnerDic(mssql_conn_str);
            MsSqlDAL.ClearAllECOStatus(mssql_conn_str);
            progressBar.Value = 0;
            progressBar.Minimum = 0;
            progressBar.Maximum = dic.Count;
            foreach (string avatar in dic.Keys)
            {
                progressBar.Value += 1;
                MsSqlDAL.UpdateECO(mssql_conn_str, avatar);
            }
            */

            //4、
        }


        public static MemberInfo GetMemberInfoFromMySql(MySqlDataReader reader)
        {
            MemberInfo obj = new MemberInfo();
            obj.id = ReadInt(reader, "id");
            obj.robot_id = ReadString(reader, "robot_id");
            obj.active_robot_wx_id = ReadString(reader, "active_robot_wx_id");
            obj.wx_id = ReadString(reader, "wx_id");
            obj.wx_number = ReadString(reader, "wx_number");
            obj.wx_name = ReadString(reader, "wx_name");
            obj.wx_img = ReadString(reader, "wx_img");
            obj.wx_note = ReadString(reader, "wx_note");
            obj.user_type = ReadIntNull(reader, "user_type");
            obj.ali_pay_name = ReadString(reader, "ali_pay_name");
            obj.ali_pay_user = ReadString(reader, "ali_pay_user");
            obj.group_id = ReadIntNull(reader, "group_id");
            obj.group_name = ReadString(reader, "group_name");
            obj.tb_pid = ReadString(reader, "tb_pid");
            obj.tb_pid_ad_zone_id = ReadString(reader, "tb_pid_ad_zone_id");
            obj.jd_pid = ReadString(reader, "jd_pid");
            obj.pdd_pid = ReadString(reader, "pdd_pid");
            obj.father_id = ReadString(reader, "father_id");
            obj.open_id = ReadString(reader, "open_id");
            obj.custom_open_id = ReadString(reader, "custom_open_id");
            obj.update_money_time = ReadDateTimeNull(reader, "update_money_time");
            obj.update_order_time = ReadDateTimeNull(reader, "update_order_time");
            obj.update_retrieve_time = ReadDateTimeNull(reader, "update_retrieve_time");
            obj.update_search_coupon_time = ReadDateTimeNull(reader, "update_search_coupon_time");
            obj.tb_order_sixbit = ReadString(reader, "tb_order_sixbit");
            obj.special_id = ReadLongNull(reader, "special_id");
            obj.relation_id = ReadLongNull(reader, "relation_id");
            obj.invite_poster_code = ReadString(reader, "invite_poster_code");
            obj.pay_order_fee = ReadDecimal(reader, "pay_order_fee");
            obj.pub_order_fee = ReadDecimal(reader, "pub_order_fee");
            obj.inv_order_fee = ReadDecimal(reader, "inv_order_fee");
            obj.for_order_fee = ReadDecimal(reader, "for_order_fee");
            obj.pay_order_comm = ReadDecimal(reader, "pay_order_comm");
            obj.pub_order_comm = ReadDecimal(reader, "pub_order_comm");
            obj.inv_order_comm = ReadDecimal(reader, "inv_order_comm");
            obj.for_order_comm = ReadDecimal(reader, "for_order_comm");
            obj.pay_order_num = ReadInt(reader, "pay_order_num");
            obj.pub_order_num = ReadInt(reader, "pub_order_num");
            obj.inv_order_num = ReadInt(reader, "inv_order_num");
            obj.for_order_num = ReadInt(reader, "for_order_num");
            obj.invite_fee = ReadDecimal(reader, "invite_fee");
            obj.effec_money = ReadDecimal(reader, "effec_money");
            obj.processing_money = ReadDecimal(reader, "processing_money");
            obj.take_money = ReadDecimal(reader, "take_money");
            obj.fixed_rate = ReadIntNull(reader, "fixed_rate");
            obj.exclusive_rate = ReadIntNull(reader, "exclusive_rate");
            obj.father_rate = ReadIntNull(reader, "father_rate");
            obj.sign_in_fee = ReadDecimal(reader, "sign_in_fee");
            obj.sign_in_time = ReadDateTimeNull(reader, "sign_in_time");
            obj.sign_in_count = ReadIntNull(reader, "sign_in_count");
            obj.sign_in_always_count = ReadIntNull(reader, "sign_in_always_count");
            obj.msg_star = ReadIntNull(reader, "msg_star");
            obj.migrate = ReadIntNull(reader, "migrate");
            obj.create_time = ReadDateTimeNull(reader, "create_time");
            obj.update_time = ReadDateTimeNull(reader, "update_time");


            return obj;
        }



        //临时增加
        public static MemberInfo GetMemberInfo(string mssql_conn_str, string Owner, string wx_id)
        {

            MemberInfo memberinfo = null;
            SqlConnection conn = new SqlConnection(mssql_conn_str);

            try
            {
                string sql = "select * ECO_Member  where Owner=@Owner and wx_id=@wx_id";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Owner", Owner);
                cmd.Parameters.AddWithValue("@wx_id", wx_id);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {

                        //   memberinfo = GetMemberInfoFromMySql(reader);
                    }
                }
                return memberinfo;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
